﻿CREATE PROCEDURE [dbo].[sp_Map_GetZipsByTerrAndCity]
(
	@AccountID varchar(10),
	@Month INT,
	@Year INT,
	@Type INT,
	@Terr varchar(max),
	@City varchar(max)
)
AS
BEGIN
	SET NOCOUNT ON
	
	IF (@Type = 0)
		BEGIN
			select zh.ZipID, zl.ZipCode as ZipName, zh.MdNumber, zl.City, zl.Territory,
			zl.ActiveLeadCount, zl.LapsedLeadCount, zl.BusinesLeadCount, zl.NeighborLeadCount,
			zh.Zip, zl.Latitude, zl.Longitude, 
			(select max(AgentName) from AssignmentDetail  where ZipID = zl.ZipID and PeriodMonth = @Month and PeriodYear = @Year) as "AgentName", 
			zh.One, zh.OrderDate, zl.PreferCity, zl.MaxPcLeadType, zl.ActiveStopCount, zl.LapsedStopCount
			from (
				select z.ZipID, zi.MdNumber, z.Zip,
				sum(case when o.PeroidMonth = @Month and o.PeroidYear = @Year then 1 else 0 end) as One,
				max(zo.OrderDate) as OrderDate
				from dbo.Zips z
				inner join dbo.ZipInfo zi on zi.ZipID = z.ZipID
				left join dbo.OrderZip oz on z.ZipID = oz.ZipID
				left join dbo.Orders o on o.OrderID = oz.OrderID
				left join 
				(
					select z.ZipID as ZipID, max(dbo.sf_GetOrderDate(o.PeroidYear, o.PeroidMonth)) as OrderDate from dbo.Zips z
					inner join dbo.ZipInfo zi on zi.ZipID = z.ZipID
					inner join dbo.OrderZip oz on z.ZipID = oz.ZipID 
					inner join dbo.Orders o on o.OrderID = oz.OrderID and o.IsPurchased = 1
					where zi.MdNumber = @AccountID and (o.PeroidMonth <> @Month or o.PeroidYear <> @Year)
					group by z.ZipID
				)zo on zo.ZipID = z.ZipID
				where zi.MdNumber = @AccountID 
				and zi.Territory in (select subcolstr from udf_CharlistToTable(@Terr, ','))
				and zi.City in (select subcolstr from udf_CharlistToTable(@City, ','))
				group by z.ZipID, z.Zip, zi.MdNumber
			)zh inner join ZipInfo zl
			on zh.ZipID = zl.ZipID
			where zl.MdNumber = @AccountID order by zl.Territory, zl.City, zh.Zip
		END
	ELSE
		BEGIN
			select zh.ZipID, zl.ZipCode as ZipName, zh.MdNumber, zl.City, zl.Territory,
			case when zh.Type = 2 then 0 else zl.ActiveLeadCount end,
			case when zh.Type = 2 then 0 else zl.LapsedLeadCount end,
			case when zh.Type = 1 then 0 else zl.BusinesLeadCount end,
			case when zh.Type = 2 then 0 else zl.NeighborLeadCount end, 
			zh.Zip, zl.Latitude, zl.Longitude, (select max(AgentName) from AssignmentDetail  where ZipID = zl.ZipID and PeriodMonth = @Month and PeriodYear = @Year) as "AgentName",
			zh.Type, zl.PreferCity, zl.MaxPcLeadType, zl.ActiveStopCount, zl.LapsedStopCount
			from (
				select z.ZipID, zi.MdNumber, z.Zip, oz.Type
				from dbo.Zips z
				inner join dbo.ZipInfo zi on zi.ZipID = z.ZipID
				left join dbo.OrderZip oz on z.ZipID = oz.ZipID
				left join dbo.Orders o on o.OrderID = oz.OrderID
				where zi.MdNumber = @AccountID
				and zi.Territory in (select subcolstr from udf_CharlistToTable(@Terr, ','))
				and zi.City in (select subcolstr from udf_CharlistToTable(@City, ','))
				group by z.ZipID, z.Zip, zi.MdNumber, oz.Type
				having sum(case when o.PeroidYear = @Year and o.PeroidMonth = @Month then 1 else 0 end) > 0
			)zh inner join ZipInfo zl
			on zh.ZipID = zl.ZipID
			where zl.MdNumber = @AccountID order by zl.Territory, zl.City, zh.Zip
		END
END
